﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Threading;
using System.Windows.Forms;
using DevExpress.XtraEditors;
using SoufunData.DB;

namespace SoufunData
{
    public partial class ExToDbZYuShouZheng : DevExpress.XtraEditors.XtraForm
    {
        public ExToDbZYuShouZheng()
        {
            InitializeComponent();
        }

        private void btn_excelPath_ButtonClick(object sender, DevExpress.XtraEditors.Controls.ButtonPressedEventArgs e)
        {

            openFileDialog1.Filter = "Excel文件|*.xls;*.xlsx";
            openFileDialog1.FileName = "";
            openFileDialog1.ShowDialog();
            string fileName = openFileDialog1.FileName;
            btn_excelPath.Text = fileName;

            gridControl2.DataSource = null;
            gridView2.Columns.Clear();
            gridView2.GroupPanelText = "请选择Excel文件";
            if (fileName == "")
            {
                return;
            }
            DataSet ds = Common.LoadDataFromExcel(fileName);
            if (ds == null || ds.Tables.Count == 0 )
            {
                string strError = "读取Excel时发生错误，请核对：\r\n ";
                strError += "1，导入时请在Excel中关闭想要导入的文件！\r\n";
                strError += "2，导入的Excel表中的工作表名称必须为Sheet1,否者程序无法读取！\r\n";
                strError += "3，请确保您的计算机安装了Microsoft Office！\r\n";
                strError += "4，请确保导入的Excel文件正常！";
                XtraMessageBox.Show(strError);
                btn_excelPath.Text = "";
                return;
            }
            if (!CheckExcelColumnsName(ds.Tables[0]))
            {                 
                btn_excelPath.Text = "";
                return;
            }

            gridControl2.DataSource = ds.Tables[0];
            gridView2.GroupPanelText = "共有" + ds.Tables[0].Rows.Count + "条数据";
        }


        private void AddData()
        {
           
            DataTable dtOld = (DataTable)gridControl2.DataSource;

            DataTable dt = dtOld.Copy();
            dt.Rows.Clear();
            foreach (DataRow dataRow in dtOld.Rows)
            {                 
                dt.Rows.Add(dataRow.ItemArray);
            }
             
            int insertRowsCount = 500;//一次性插入数据库行数
            int succeedrows = 0;//成功插入数据行数
            StringBuilder strSql = new StringBuilder();

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                DataRow row = dt.Rows[i];
                string strNumber = dt.Rows[i]["预售许可证号"].ToString();
                string strProName = dt.Rows[i]["项目名称"].ToString();
                string strCompany = dt.Rows[i]["企业名称"].ToString();
                string strGroup = dt.Rows[i]["所属集团"].ToString();
                string strProAdd = dt.Rows[i]["项目地址"].ToString();
                string strXzq = dt.Rows[i]["行政区"].ToString();
                string strBoard = dt.Rows[i]["版块"].ToString();
                string strPubTime = dt.Rows[i]["公告时间"].ToString();
                string strGrantPermintTime = dt.Rows[i]["发证时间"].ToString();
                string strBuildNum = dt.Rows[i]["施工许可证号"].ToString();
                string strPlanNum = dt.Rows[i]["规划许可证号"].ToString();
                string strMonitorId = dt.Rows[i]["预售资金监管账号"].ToString();
                string strPreSellBuilding = dt.Rows[i]["核准预售楼栋"].ToString();
                string strPreSellCount = dt.Rows[i]["核准预售总套数"].ToString();
                if (strPreSellCount.Trim()=="")
                {
                    strPreSellCount = "0";
                }
                string strPreSellArea = dt.Rows[i]["核准预售总面积"].ToString();
                if (strPreSellArea.Trim()=="")
                {
                    strPreSellArea = "0";
                }
                string strHzwyxt = dt.Rows[i]["核准预售物业形态"].ToString();
                string strBuildId = dt.Rows[i]["楼栋号"].ToString();
                string strFloorCount = dt.Rows[i]["层高"].ToString();
                string strFloorImage = dt.Rows[i]["楼层平面图"].ToString();
                string strUnitCount = dt.Rows[i]["单元个数"].ToString();
                string strLiftCount = dt.Rows[i]["电梯个数"].ToString();
                string strHouseCount = dt.Rows[i]["户数"].ToString();
                string strHouseType = dt.Rows[i]["户型"].ToString();
                string strHouseArea = dt.Rows[i]["户型面积（㎡）"].ToString();
                string strHousePreSellCount = dt.Rows[i]["分户型预售套数（套）"].ToString();
                string strHousePreSellArea = dt.Rows[i]["分户型预售面积"].ToString();

                if (i % insertRowsCount == 0)
                {
                    strSql = new StringBuilder();
                    strSql.Append("Insert into tzyushouzheng (");
                    strSql.Append("Number,");
                    strSql.Append("ProjectName,");
                    strSql.Append("Company,");
                    strSql.Append("`Group`,");
                    strSql.Append("ProjectAddress,");
                    strSql.Append("AdministratiVeregion,");
                    strSql.Append("Board,");
                    strSql.Append("PublicTime,");
                    strSql.Append("GrantPermitTime,");
                    strSql.Append("BuildNumber,");
                    strSql.Append("PlanNumber,");
                    strSql.Append("MonitorId,");
                    strSql.Append("PreSellBuilding,");
                    strSql.Append("PreSellCount,");
                    strSql.Append("PreSellArea,");
                    strSql.Append("PropertyType,");
                    strSql.Append("BuildId,");
                    strSql.Append("FloorCount,");
                    strSql.Append("FloorImage,");
                    strSql.Append("UnitCount,");
                    strSql.Append("LiftCount,");
                    strSql.Append("HouseCount,");
                    strSql.Append("HouseType,");
                    strSql.Append("HouseArea,");
                    strSql.Append("HousePreSellCount,");
                    strSql.Append("HousePreSellArea)");
                    strSql.Append(" value  ");
                }
                strSql.Append("(");
                strSql.Append("'");
                strSql.Append(strNumber);
                strSql.Append("','");
                strSql.Append(strProName);
                strSql.Append("','");
                strSql.Append(strCompany);
                strSql.Append("','");
                strSql.Append(strGroup);
                strSql.Append("','");
                strSql.Append(strProAdd);
                strSql.Append("','");
                strSql.Append(strXzq);
                strSql.Append("','");
                strSql.Append(strBoard);
                strSql.Append("','");
                strSql.Append(strPubTime);
                strSql.Append("','");
                strSql.Append(strGrantPermintTime);
                strSql.Append("','");
                strSql.Append(strBuildNum);
                strSql.Append("','");
                strSql.Append(strPlanNum);
                strSql.Append("','");
                strSql.Append(strMonitorId);
                strSql.Append("','");
                strSql.Append(strPreSellBuilding);
                strSql.Append("',");                
                strSql.Append(strPreSellCount);
                strSql.Append(",");
                strSql.Append(strPreSellArea);
                strSql.Append(",'");
                strSql.Append(strHzwyxt);
                strSql.Append("','");
                strSql.Append(strBuildId);
                strSql.Append("','");
                strSql.Append(strFloorCount);
                strSql.Append("','");
                strSql.Append(strFloorImage);
                strSql.Append("','");
                strSql.Append(strUnitCount);
                strSql.Append("','");
                strSql.Append(strLiftCount);
                strSql.Append("','");
                strSql.Append(strHouseCount);
                strSql.Append("','");
                strSql.Append(strHouseType);
                strSql.Append("','");
                strSql.Append(strHouseArea);
                strSql.Append("',");
                strSql.Append(strHousePreSellCount);
                strSql.Append(",");
                strSql.Append(strHousePreSellArea);                 
                strSql.Append("),");
                if ((i + 1) % insertRowsCount == 0
                    || i == dt.Rows.Count - 1)
                {
                    strSql.Remove(strSql.Length - 1, 1);
                    succeedrows += DbHelper.ExecuteSql(strSql.ToString());
                }
            }

            if (dt.Rows.Count == succeedrows)
            {
                XtraMessageBox.Show("添加成功！");
                BeginInvoke(new MethodInvoker(() =>
                {
                    btn_excelPath.Text = "";
                    gridControl2.DataSource = null;
                }));
            }
            else
            {
                int failedCount = dt.Rows.Count - succeedrows;
                XtraMessageBox.Show("共记" + dt.Rows.Count + "条数据，其中"
                    + failedCount + "条数据添加失败！");
            }
            BeginInvoke(new MethodInvoker(() =>
            {
               
                btn_excelPath.Enabled = true;
                btn_add.Enabled = true;
            }));
        }

        private void btn_add_Click(object sender, EventArgs e)
        {
            if (btn_excelPath.Text.Trim()=="")
            {
                XtraMessageBox.Show("请先选择Excel！");
                return;
            }
            if (!CheckDataValid())
            {
                return;
            }
             
            btn_excelPath.Enabled = false;
            btn_add.Enabled = false;
            Thread t = new Thread(AddData) { IsBackground = true };
            t.Start();
        }

      
        #region 数据完整性检测
        private bool CheckDataValid()
        {
            DataTable dt = (DataTable)gridControl2.DataSource;
            StringBuilder strErrors = new StringBuilder();

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                DataRow dr = dt.Rows[i];
                string strProName = dr["项目名称"].ToString();
                string strCompany = dr["企业名称"].ToString();
                string strGroup = dr["所属集团"].ToString();
                string strXzq = dr["行政区"].ToString();
                string strBoard = dr["版块"].ToString();
                string strPubTime = dr["公告时间"].ToString();
                string strFzTime = dr["发证时间"].ToString();
                string strProType = dr["核准预售物业形态"].ToString();
                string strHouseType = dr["户型"].ToString();

                string strHzyszts = dr["核准预售总套数"].ToString();
                string strHzyszmj = dr["核准预售总面积"].ToString();
                string strFhxysts = dr["分户型预售套数（套）"].ToString();
                string strFhxysmj = dr["分户型预售面积"].ToString();


                if (strProName.Trim() == "")
                {
                    strErrors.Append("第" + (i + 2) + "行数据中的【项目名称】为空！\r\n");
                }
                if (strCompany.Trim() == "")
                {
                    strErrors.Append("第" + (i + 2) + "行数据中的【企业名称】为空！\r\n");
                }
                if (strGroup.Trim() == "")
                {
                    strErrors.Append("第" + (i + 2) + "行数据中的【所属集团】为空！\r\n");
                }
                if (strXzq.Trim() == "")
                {
                    strErrors.Append("第" + (i + 2) + "行数据中【行政区】未填！\r\n");
                }
                if (strBoard.Trim() == "")
                {
                    strErrors.Append("第" + (i + 2) + "行数据中【板块】未填！\r\n");
                }
                DateTime dateTemp = new DateTime();
                if (!DateTime.TryParse(strPubTime,out dateTemp))
                {
                    strErrors.Append("第" + (i + 2) + "行数据中【公告时间】不是正确的时间格式！\r\n");
                }
                if (!DateTime.TryParse(strFzTime, out dateTemp))
                {
                    strErrors.Append("第" + (i + 2) + "行数据中【发证时间】不是正确的时间格式！\r\n");
                }
                if (strProType.Trim() == "")
                {
                    strErrors.Append("第" + (i + 2) + "行数据中的【核准预售物业形态】为空！\r\n");
                }
                if (strHouseType.Trim() == "")
                {
                    strErrors.Append("第" + (i + 2) + "行数据中的【户型】为空！\r\n");
                }
                int intTemp = 0;
                float floatTem = 0;
                if (strHzyszts.Trim()!="")
                {
                  if (!int.TryParse(strHzyszts, out  intTemp))
                  {
                     strErrors.Append("第" + (i + 2) + "行数据中的【核准预售总套数】不是正确的整数格式！\r\n");
                  }     
                }
                if (strHzyszmj.Trim()!="")
                {
                    if (!float.TryParse(strHzyszmj, out floatTem))
                    {
                        strErrors.Append("第" + (i + 2) + "行数据中的【核准预售总面积】不是正确的数字格式！\r\n");
                    }     
                }
               
                if (!int.TryParse(strFhxysts, out intTemp))
                {
                    strErrors.Append("第" + (i + 2) + "行数据中的【分户型预售套数（套）】不是正确的整数格式！\r\n");
                }    
                
                
                if (!float.TryParse(strFhxysmj, out floatTem))
                {
                    strErrors.Append("第" + (i + 2) + "行数据中的【分户型预售面积】不是正确的数字格式！\r\n");
                }    
                           
            }
            if (strErrors.ToString() != "")
            {
                XtraMessageBox.Show("您选择的Excel中的数据存在以下错误：\r\n" +
                    strErrors);
                return false;
            }
            return true;
        }

        private bool CheckExcelColumnsName(DataTable dt)
        {
            StringBuilder strErrors = new StringBuilder();
            if (!dt.Columns.Contains("预售许可证号"))
            {
                strErrors.Append("【预售许可证号】、");
            }
            if (!dt.Columns.Contains("项目名称"))
            {
                strErrors.Append("【项目名称】、");
            }
            if (!dt.Columns.Contains("企业名称"))
            {
                strErrors.Append("【企业名称】、");
            }
            if (!dt.Columns.Contains("所属集团"))
            {
                strErrors.Append("【所属集团】、");
            }
            if (!dt.Columns.Contains("项目地址"))
            {
                strErrors.Append("【项目地址】、");
            }
            if (!dt.Columns.Contains("行政区"))
            {
                strErrors.Append("【行政区】、");
            }
            if (!dt.Columns.Contains("版块"))
            {
                strErrors.Append("【版块】、");
            }
            if (!dt.Columns.Contains("公告时间"))
            {
                strErrors.Append("【公告时间】、");
            }
            if (!dt.Columns.Contains("发证时间"))
            {
                strErrors.Append("【发证时间】、");
            }
            if (!dt.Columns.Contains("施工许可证号"))
            {
                strErrors.Append("【施工许可证号】、");
            }
            if (!dt.Columns.Contains("规划许可证号"))
            {
                strErrors.Append("【规划许可证号】");
            }
            if (!dt.Columns.Contains("预售资金监管账号"))
            {
                strErrors.Append("【预售资金监管账号】");
            }
            if (!dt.Columns.Contains("核准预售楼栋"))
            {
                strErrors.Append("【核准预售楼栋】");
            }
            if (!dt.Columns.Contains("核准预售总套数"))
            {
                strErrors.Append("【核准预售总套数】");
            }
            if (!dt.Columns.Contains("核准预售总面积"))
            {
                strErrors.Append("【核准预售总面积】");
            }
            if (!dt.Columns.Contains("核准预售物业形态"))
            {
                strErrors.Append("【核准预售物业形态】");
            }
            if (!dt.Columns.Contains("楼栋号"))
            {
                strErrors.Append("【楼栋号】");
            }
            if (!dt.Columns.Contains("层高"))
            {
                strErrors.Append("【层高】");
            }
            if (!dt.Columns.Contains("楼层平面图"))
            {
                strErrors.Append("【楼层平面图】");
            }
            if (!dt.Columns.Contains("单元个数"))
            {
                strErrors.Append("【单元个数】");
            }
            if (!dt.Columns.Contains("电梯个数"))
            {
                strErrors.Append("【电梯个数】");
            }
            if (!dt.Columns.Contains("户数"))
            {
                strErrors.Append("【户数】");
            }
            if (!dt.Columns.Contains("户型"))
            {
                strErrors.Append("【户型】");
            }
            if (!dt.Columns.Contains("户型面积（㎡）"))
            {
                strErrors.Append("【户型面积（㎡）】");
            }
            if (!dt.Columns.Contains("分户型预售套数（套）"))
            {
                strErrors.Append("【分户型预售套数（套）】");
            }
            if (!dt.Columns.Contains("分户型预售面积"))
            {
                strErrors.Append("【分户型预售面积】");
            }
           
            if (strErrors.ToString() != "")
            {
                XtraMessageBox.Show("您选择的Excel表数据不正常,缺少以下数据列:\r\n" +
                                    strErrors);
                return false;
            }
            return true;
        }
        #endregion
    }
}